Freelog Special Freeware 31/FreelogHS31.iso/ArgentCompta/FASTCOMPTA/Setup FASTCOMPTA.exe/{app}/createbase.sql

Text File | 2006-12-20 | 66KB | 3,046 lines

SET SQL DIALECT 3;

DECLARE EXTERNAL FUNCTION ABS
DOUBLE PRECISION
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_abs' MODULE_NAME 'ib_udf';


DECLARE EXTERNAL FUNCTION ASCII_CHAR
INTEGER
RETURNS CSTRING(1) CHARACTER SET NONE FREE_IT
ENTRY_POINT 'IB_UDF_ascii_char' MODULE_NAME 'ib_udf';


DECLARE EXTERNAL FUNCTION ASCII_VAL
CHAR(1) CHARACTER SET NONE
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_ascii_val' MODULE_NAME 'ib_udf';


DECLARE EXTERNAL FUNCTION STRLEN
CSTRING(32767) CHARACTER SET NONE
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';


DECLARE EXTERNAL FUNCTION SUBSTR
CSTRING(80) CHARACTER SET NONE, SMALLINT, SMALLINT
RETURNS CSTRING(80) CHARACTER SET NONE FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';


DECLARE EXTERNAL FUNCTION SUBSTRLEN
CSTRING(32000) CHARACTER SET NONE, SMALLINT, SMALLINT
RETURNS CSTRING(255) CHARACTER SET NONE FREE_IT
ENTRY_POINT 'IB_UDF_substrlen' MODULE_NAME 'ib_udf';


/* Domain definitions */
CREATE DOMAIN "T_BOOLEAN" AS SMALLINT
     DEFAULT 0;
CREATE DOMAIN "T_COEFF" AS DECIMAL(5, 3);
CREATE DOMAIN "T_EURO" AS DECIMAL(12, 2);
CREATE DOMAIN "T_SENS" AS CHAR(1)
     DEFAULT 'D'
     CHECK (VALUE IN ('D','C'));

/* Table: BLOBS, Owner: SYSDBA */

CREATE TABLE "BLOBS"
(
  "INDX"    INTEGER NOT NULL,
  "BINAIRE"    BLOB SUB_TYPE 0 SEGMENT SIZE 80,
 PRIMARY KEY ("INDX")
);

/* Table: COMPTES, Owner: SYSDBA */

CREATE TABLE "COMPTES"
(
  "INDX"    INTEGER NOT NULL,
  "LIB_COURT"    VARCHAR(15),
  "LIB_LONG"    VARCHAR(80),
  "INDX_TIERS"    INTEGER,
  "REGROUPEMENT"    INTEGER,
  "INDX_TVA"    SMALLINT,
  "VERROU"    "T_BOOLEAN",
  "CONTREPARTIE"    INTEGER,
 PRIMARY KEY ("INDX")
);

/* Table: COMPTEUR, Owner: SYSDBA */

CREATE TABLE "COMPTEUR"
(
  "INDX_EXERCICE"    INTEGER NOT NULL,
  "INDX_JOURNAL"    INTEGER,
  "PREFIXE"    VARCHAR(5),
  "SUFFIXE"    VARCHAR(5),
  "INCREMENT"    SMALLINT DEFAULT 2
);

/* Table: ETATS, Owner: SYSDBA */

CREATE TABLE "ETATS"
(
  "INDX"    INTEGER NOT NULL,
  "NOM"    VARCHAR(200),
  "FRF"    BLOB SUB_TYPE 0 SEGMENT SIZE 80,
  "NOFONCTION"    SMALLINT,
 PRIMARY KEY ("INDX")
);

/* Table: EXERCICE, Owner: SYSDBA */

CREATE TABLE "EXERCICE"
(
  "INDX"    SMALLINT NOT NULL,
  "DATEDEB"    DATE,
  "DATEFIN"    DATE,
  "DESIGNATION"    VARCHAR(40),
  "CLOS"    "T_BOOLEAN",
  "DATE_CLOTURE"    DATE,
  "MVT_A_NOUVEAU"    INTEGER,
  "MVT_401"    INTEGER,
  "MVT_411"    INTEGER,
 PRIMARY KEY ("INDX")
);

/* Table: JOURNAL, Owner: SYSDBA */

CREATE TABLE "JOURNAL"
(
  "INDX"    SMALLINT NOT NULL,
  "SHORTDESI"    VARCHAR(4),
  "LONGDESI"    VARCHAR(80),
  "TYPEJX"    SMALLINT,
  "NUMEROTATION"    SMALLINT,
  "INDX_COMPTE"    INTEGER,
 PRIMARY KEY ("INDX")
);

/* Table: JOURNAL_MOIS, Owner: SYSDBA */

CREATE TABLE "JOURNAL_MOIS"
(
  "INDX"    SMALLINT NOT NULL,
  "INDX_EXERCICE"    SMALLINT NOT NULL,
  "INDX_JOURNAL"    SMALLINT NOT NULL,
  "NOMOIS"    SMALLINT,
  "MOIS"    SMALLINT,
  "SOLDE_COMPTE"    "T_EURO",
  "DATE_PRN"    DATE,
  "DATE_CLOTURE"    DATE,
 PRIMARY KEY ("INDX")
);

/* Table: LIGNES, Owner: SYSDBA */

CREATE TABLE "LIGNES"
(
  "INDX_MVT"    INTEGER NOT NULL,
  "LIGNE"    SMALLINT NOT NULL,
  "INDXE"    SMALLINT,
  "INDX_CPT"    INTEGER NOT NULL,
  "INDX_RGP"    INTEGER,
  "LIBELLE"    VARCHAR(80),
  "MONTANT"    "T_EURO",
  "SENS"    "T_SENS",
  "LETTRAGE"    VARCHAR(4),
 PRIMARY KEY ("INDX_MVT", "LIGNE")
);

/* Table: LISTE, Owner: SYSDBA */

CREATE TABLE "LISTE"
(
  "NOLISTE"    INTEGER,
  "INDX"    INTEGER,
  "NOLIGNE"    SMALLINT
);

/* Table: MODELES, Owner: SYSDBA */

CREATE TABLE "MODELES"
(
  "INDX"    SMALLINT NOT NULL,
  "INDX_BLOB"    INTEGER NOT NULL,
  "DESIGNATION"    VARCHAR(80),
  "SHORTKEY"    INTEGER,
  "ISLETTRAGE"    "T_BOOLEAN",
 PRIMARY KEY ("INDX")
);

/* Table: MOUVEMENTS, Owner: SYSDBA */

CREATE TABLE "MOUVEMENTS"
(
  "INDX"    INTEGER NOT NULL,
  "INDX_EXERCICE"    SMALLINT,
  "INDX_JOURNAL_M"    SMALLINT,
  "DATE_MVT"    DATE,
  "NO_INTERNE"    VARCHAR(15),
  "NO_EXTERNE"    VARCHAR(15),
  "VERROU"    "T_BOOLEAN",
  "PRINTED"    "T_BOOLEAN",
  "IMPAIRE"    "T_BOOLEAN",
  "SOLDE_PROG"    "T_EURO",
  "DATE_CLOTURE"    DATE,
  "DATE_PONT"    TIMESTAMP,
  "DATE_MODIF"    TIMESTAMP DEFAULT 'NOW',
 PRIMARY KEY ("INDX")
);

/* Table: PREFERENCES, Owner: SYSDBA */

CREATE TABLE "PREFERENCES"
(
  "TYPEPREF"    SMALLINT,
  "VALEUR"    INTEGER,
  "INDX"    INTEGER
);

/* Table: TIERS, Owner: SYSDBA */

CREATE TABLE "TIERS"
(
  "INDX"    INTEGER NOT NULL,
  "CODEMAGASIN"    VARCHAR(15),
  "NOM"    VARCHAR(60),
  "ADRESSE"    VARCHAR(26),
  "ADRESSE2"    VARCHAR(26),
  "LOCALITE"    VARCHAR(21),
  "CODEPOSTAL"    VARCHAR(8),
  "VILLE"    VARCHAR(22),
  "PAYS"    VARCHAR(26),
  "TELEPHONE"    VARCHAR(15),
  "TELECOPIE"    VARCHAR(15),
  "GSM"    VARCHAR(15),
  "WWW"    VARCHAR(80),
  "EMAIL"    VARCHAR(80),
  "CONTACT"    VARCHAR(80),
  "NOMBANQUE"    VARCHAR(20),
  "RIBBANQUE"    VARCHAR(5),
  "RIBGUICHET"    VARCHAR(5),
  "RIBCOMPTE"    VARCHAR(12),
  "RIBCLE"    VARCHAR(2),
  "COMPTGENE"    VARCHAR(13),
  "COMPTAUXI"    VARCHAR(13),
  "NIFCEE"    VARCHAR(14),
  "SECTEUR"    SMALLINT,
  "DATECREATION"    DATE DEFAULT 'NOW',
  "DATEMODIF"    DATE,
  "ENCOURS"    "T_EURO",
  "PLAFOND"    "T_EURO",
  "ACOMPTE"    "T_EURO",
  "AVOIR"    "T_EURO",
  "TYPEREG"    SMALLINT DEFAULT 0,
  "APE"    VARCHAR(4),
  "SIRET"    VARCHAR(18),
  "CBNUM"    VARCHAR(20),
  "CBVALIDE"    VARCHAR(8),
  "CBPORTEUR"    VARCHAR(35),
 PRIMARY KEY ("INDX")
);

/* Table: TVA, Owner: SYSDBA */

CREATE TABLE "TVA"
(
  "INDX"    SMALLINT NOT NULL,
  "DESIGNATION"    VARCHAR(40),
  "VALEUR"    DECIMAL(4, 2),
 PRIMARY KEY ("INDX")
);

/*  Index definitions for all user tables */

CREATE INDEX "LIGNES_IDX1" ON "LIGNES"("INDX_CPT");
CREATE INDEX "LIGNES_IDX2" ON "LIGNES"("LETTRAGE");
CREATE INDEX "LIGNES_IDX3" ON "LIGNES"("INDXE");
ALTER TABLE "COMPTEUR" ADD FOREIGN KEY ("INDX_EXERCICE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "COMPTEUR" ADD FOREIGN KEY ("INDX_EXERCICE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "JOURNAL_MOIS" ADD FOREIGN KEY ("INDX_EXERCICE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "JOURNAL_MOIS" ADD FOREIGN KEY ("INDX_JOURNAL") REFERENCES "JOURNAL" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "JOURNAL_MOIS" ADD FOREIGN KEY ("INDX_EXERCICE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "JOURNAL_MOIS" ADD FOREIGN KEY ("INDX_JOURNAL") REFERENCES "JOURNAL" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "LIGNES" ADD CONSTRAINT "FK_LIGNES_1" FOREIGN KEY ("INDXE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "LIGNES" ADD FOREIGN KEY ("INDX_MVT") REFERENCES "MOUVEMENTS" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "MOUVEMENTS" ADD FOREIGN KEY ("INDX_EXERCICE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "MOUVEMENTS" ADD FOREIGN KEY ("INDX_JOURNAL_M") REFERENCES "JOURNAL_MOIS" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "MOUVEMENTS" ADD FOREIGN KEY ("INDX_EXERCICE") REFERENCES "EXERCICE" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "MOUVEMENTS" ADD FOREIGN KEY ("INDX_JOURNAL_M") REFERENCES "JOURNAL_MOIS" ("INDX") ON UPDATE CASCADE ON DELETE CASCADE;

CREATE GENERATOR "GENPK_BLOBS";
CREATE GENERATOR "GENPK_COMPTES";
CREATE GENERATOR "GENPK_ETATS";
CREATE GENERATOR "GENPK_EXERCICE";
CREATE GENERATOR "GENPK_JOURNAL";
CREATE GENERATOR "GENPK_JOURNAL_MOIS";
CREATE GENERATOR "GENPK_MODELES";
CREATE GENERATOR "GENPK_MOUVEMENTS";
CREATE GENERATOR "GENPK_TIERS";
CREATE GENERATOR "GENPK_TVA";
CREATE GENERATOR "GEN_1";
CREATE GENERATOR "GEN_1_1";
CREATE GENERATOR "GEN_1_2";
CREATE GENERATOR "GEN_1_6";
CREATE GENERATOR "NOLISTE";
CREATE GENERATOR "VERSION_BASE";
ALTER TABLE "JOURNAL_MOIS" ADD
CHECK ("MOIS" BETWEEN 1 AND 12);

ALTER TABLE "JOURNAL_MOIS" ADD
CHECK ("MOIS" BETWEEN 1 AND 12);


/*  Exceptions */

CREATE EXCEPTION "DEL_VERROU" 'Ce mouvement est verrouillΘ. Effacement impossible';
CREATE EXCEPTION "MAJ_VERROU" 'Ce mouvement est verrouillΘ. Mise α jour impossible';
CREATE EXCEPTION "OUT_OF_LETTRES" 'Plus de lettrage disponible !!!';
COMMIT WORK;
SET AUTODDL OFF;

/* Stored procedures */

CREATE PROCEDURE "ALL_SOLDE_COMPTE"
(
  "EXERCICE" SMALLINT
)
RETURNS
(
  "INDX_JOURNAL_MOIS" SMALLINT,
  "INDEX_COMPTE" INTEGER,
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "A_NOUVEAU_COMPTE"
(
  "INDEX_EXERCICE" SMALLINT,
  "INDEX_COMPTE" INTEGER
)
RETURNS
(
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "BALANCE_DES_COMPTES"
(
  "INDX_EXERCICE" SMALLINT,
  "AVEC_TOTAUX" SMALLINT,
  "AVEC_TIERS" SMALLINT
)
RETURNS
(
  "INDX" INTEGER,
  "COMPTE" VARCHAR(15),
  "TIERS" VARCHAR(15),
  "DESCRIPTION" VARCHAR(80),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "SOLDED" DECIMAL(12, 2),
  "SOLDEC" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "BALANCE_UN_COMPTE"
(
  "INDX_EXERCICE" SMALLINT,
  "INDX_CPT" INTEGER
)
RETURNS
(
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "CALCUL_VERROU_COMPTES"
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "CALCUL_VERROU_UN_COMPTE"
(
  "INDEX_CPT" INTEGER
)
RETURNS
(
  "VERROU" INTEGER
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "CHECK_GENPK"
RETURNS
(
  "INFO" VARCHAR(50)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "COPIE_LETTRAGE"
(
  "MVT_1" INTEGER
)
RETURNS
(
  "INFO" VARCHAR(80)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFFACE_MODELE"
(
  "INDEX_MODELE" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_COMPTE"
(
  "INDEX_CPT" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_COMPTE_OK"
(
  "INDEX_CPT" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_EXERCICE_OK"
(
  "INDEX_E" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_JOURNAL_MOIS"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_JOURNAL_MOIS_OK"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_JOURNAL_OK"
(
  "INDEX_J" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_LISTE_COMPTE_OK"
(
  "NOLISTE" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EFF_MOUVEMENT"
(
  "INDEX_MVT" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EXPORT_ADRESSE"
(
  "INDX" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(30)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EXPORT_COMPTE"
(
  "INDX_C" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(90)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EXPORT_COMPTES"
(
  "NOLISTE" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(250)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EXPORT_TIERS"
(
  "INDX" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(250)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EXPORT_TVA"
(
  "INDX" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(250)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "EXTRAIT_COMPTE"
(
  "INDX_CPT" INTEGER,
  "INDX_EXERCICE" SMALLINT,
  "LETTRE" SMALLINT,
  "NON_LETTRE" SMALLINT
)
RETURNS
(
  "DATE_MVT" DATE,
  "CODEJ" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "SOLDEP" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_INDEXS_COMPTE"
(
  "LIB_CG" VARCHAR(15),
  "LIB_CA" VARCHAR(15)
)
RETURNS
(
  "INDX_CG" VARCHAR(15),
  "INDX_CA" VARCHAR(15)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_INDEX_JM"
(
  "DESI" VARCHAR(4),
  "MOIS" SMALLINT,
  "INDX_EXERCICE" SMALLINT
)
RETURNS
(
  "INDX" INTEGER
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_INDX_COMPTE"
(
  "LIB_CG" VARCHAR(15),
  "LIB_CA" VARCHAR(15)
)
RETURNS
(
  "INDX" INTEGER
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_LETTRAGE"
(
  "INDX_CPT" INTEGER,
  "INDX_E" INTEGER
)
RETURNS
(
  "LETTRAGE" VARCHAR(4)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_LIB_COURTS"
(
  "INDX" INTEGER
)
RETURNS
(
  "LIBGENE" VARCHAR(15),
  "LIBAUXI" VARCHAR(15)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_NEXT_LETTRAGE"
(
  "LETTRAGE" VARCHAR(4)
)
RETURNS
(
  "RESULT" VARCHAR(4)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_NOM_COMPTE"
(
  "INDX" INTEGER
)
RETURNS
(
  "NOM" VARCHAR(250)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_NOM_COMPTE_GENERAL"
(
  "INDX" INTEGER
)
RETURNS
(
  "LIBELLE" VARCHAR(15)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_REPORT_JM_COMPTE"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GET_SOLDE_JOURNAL"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "MONTANT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "GRAND_LIVRE"
(
  "INDX_EXERCICE" SMALLINT,
  "INDEX_CPT" INTEGER,
  "AVEC_TIERS" SMALLINT,
  "QUE_LETTRE" SMALLINT,
  "QUE_NON_LETTRE" SMALLINT
)
RETURNS
(
  "INDX" INTEGER,
  "DATE_MVT" DATE,
  "CODEJ" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "SOLDEP" DECIMAL(12, 2),
  "LIB_GENE" VARCHAR(15),
  "LIB_AUXI" VARCHAR(15)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "IMPORT_COMPTE"
(
  "LIB_COURT" VARCHAR(15),
  "LIB_LONG" VARCHAR(80),
  "CODEMAGASIN" VARCHAR(15),
  "REGROUPEMENT" VARCHAR(15),
  "VALEUR_TVA" VARCHAR(6)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "IMPORT_TVA"
(
  "DESIGNATION" VARCHAR(40),
  "VALEUR" DECIMAL(4, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "INTERROGATION"
(
  "INDEX_EXERCICE" INTEGER,
  "DATEDEB" DATE,
  "DATEFIN" DATE,
  "DETAILS" SMALLINT
)
RETURNS
(
  "LIBJOURNAL" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "DATE_MVT" DATE,
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "LIB_CPT" VARCHAR(80),
  "INDX_JL" SMALLINT,
  "DATEPONT" DATE
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "INTERROGATION_CPT"
(
  "INDX" INTEGER,
  "INDX_EXERCICE" SMALLINT
)
RETURNS
(
  "CODEJ" VARCHAR(4),
  "DATE_MVT" DATE,
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "INDX_CPT" INTEGER
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "INTERROGATION_JL"
(
  "INDX_JM" INTEGER
)
RETURNS
(
  "NO_INTERNE" VARCHAR(15),
  "DATE_MVT" DATE,
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "LIB_CPT" VARCHAR(80)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "INTERROGATION_LISTE"
(
  "NOLISTE" INTEGER
)
RETURNS
(
  "LIBJOURNAL" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "DATE_MVT" DATE,
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "LIB_CPT" VARCHAR(80),
  "INDX_JL" SMALLINT,
  "DATEPONT" DATE
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "INTERROGATION_MVT"
(
  "INDX_MVT" INTEGER
)
RETURNS
(
  "NOLIGNE" SMALLINT,
  "DATE_MVT" DATE,
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "IS_EFF_MOUVEMENT_OK"
(
  "INDEX_MVT" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "LISTE_COMPTES"
(
  "AVEC_TIERS" SMALLINT
)
RETURNS
(
  "INDX" INTEGER,
  "INDX_GENE" INTEGER,
  "INDX_AUXI" INTEGER,
  "COMPTE" VARCHAR(15),
  "TIERS" VARCHAR(15),
  "DESCRIPTION" VARCHAR(80)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "LISTE_JOURNAUX_MENSUELS"
(
  "INDX_E" SMALLINT,
  "TYPE_LISTE" SMALLINT
)
RETURNS
(
  "INDX_JM" INTEGER,
  "MOIS" SMALLINT,
  "CODE_J" VARCHAR(4),
  "DESI" VARCHAR(80),
  "DATE_MODIF" DATE,
  "MONTANT" DECIMAL(12, 2),
  "DATE_PRN" DATE,
  "DATE_CLOTURE" DATE,
  "NBR" INTEGER,
  "NOMOIS" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "SET_IMPAIRE"
(
  "NO_JOURNAL_MOIS" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "SOLDE_JM_COMPTE"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "INDEX_COMPTE" INTEGER,
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "UPDATE_DATE_CLOTURE_JM"
(
  "INDX_JM" SMALLINT
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "VERROUILLE_JM"
(
  "INDX_JM" INTEGER
)
AS
BEGIN EXIT; END ;

CREATE PROCEDURE "VERROUILLE_MVT"
(
  "INDX_MVT" INTEGER
)
AS
BEGIN EXIT; END ;


ALTER PROCEDURE "ALL_SOLDE_COMPTE"
(
  "EXERCICE" SMALLINT
)
RETURNS
(
  "INDX_JOURNAL_MOIS" SMALLINT,
  "INDEX_COMPTE" INTEGER,
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
BEGIN
FOR
  SELECT "INDX" FROM JOURNAL_MOIS
  WHERE INDX_EXERCICE=:EXERCICE
  ORDER BY INDX_JOURNAL,NOMOIS
  INTO :INDX_JOURNAL_MOIS
  DO
    BEGIN
    SELECT "INDEX_COMPTE","CREDIT","DEBIT"
    FROM SOLDE_JM_COMPTE(:INDX_JOURNAL_MOIS)
    INTO :INDEX_COMPTE,:CREDIT,:DEBIT;
    SUSPEND;
    END
END
 ;


ALTER PROCEDURE "A_NOUVEAU_COMPTE"
(
  "INDEX_EXERCICE" SMALLINT,
  "INDEX_COMPTE" INTEGER
)
RETURNS
(
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
BEGIN
SELECT SUM(MONTANT) from LIGNES L
JOIN MOUVEMENTS M ON M.INDX=L.INDX_MVT AND L.SENS='D' AND INDX_EXERCICE=:INDEX_EXERCICE
JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL AND J.TYPEJX=4
where l.INDX_CPT=:INDEX_COMPTE INTO :DEBIT;

SELECT SUM(MONTANT) from LIGNES L
JOIN MOUVEMENTS M ON M.INDX=L.INDX_MVT AND L.SENS='C' AND INDX_EXERCICE=:INDEX_EXERCICE
JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL AND J.TYPEJX=4
where l.INDX_CPT=:INDEX_COMPTE INTO :CREDIT;
IF (CREDIT IS NULL) THEN CREDIT=0;
IF (DEBIT IS NULL) THEN DEBIT=0;
SUSPEND;
END
 ;


ALTER PROCEDURE "BALANCE_DES_COMPTES"
(
  "INDX_EXERCICE" SMALLINT,
  "AVEC_TOTAUX" SMALLINT,
  "AVEC_TIERS" SMALLINT
)
RETURNS
(
  "INDX" INTEGER,
  "COMPTE" VARCHAR(15),
  "TIERS" VARCHAR(15),
  "DESCRIPTION" VARCHAR(80),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "SOLDED" DECIMAL(12, 2),
  "SOLDEC" DECIMAL(12, 2)
)
AS
DECLARE VARIABLE TOTC2 DECIMAL(12,2);
DECLARE VARIABLE TOTD2 DECIMAL(12,2);
DECLARE VARIABLE CLASSE6C DECIMAL(12,2);
DECLARE VARIABLE CLASSE6D DECIMAL(12,2);
DECLARE VARIABLE CLASSE7C DECIMAL(12,2);
DECLARE VARIABLE CLASSE7D DECIMAL(12,2);
begin
TOTC2 = 0;
TOTD2 = 0;
CLASSE6C = 0;
CLASSE6D = 0;
CLASSE7C = 0;
CLASSE7D = 0;

FOR SELECT INDX,COMPTE,TIERS,DESCRIPTION FROM LISTE_COMPTES(:AVEC_TIERS)
INTO :INDX,:COMPTE,:TIERS,:DESCRIPTION DO
  BEGIN
  if (AVEC_TIERS=1) then
    BEGIN
    SELECT SUM(MONTANT) from lignes
      where INDXE=:INDX_EXERCICE AND INDX_CPT=:INDX AND SENS='C'
      INTO :CREDIT;
    SELECT SUM(MONTANT) from lignes L
      where INDXE=:INDX_EXERCICE AND INDX_CPT=:INDX AND SENS='D'
      INTO :DEBIT;
    end
  ELSE
    BEGIN
    SELECT SUM(MONTANT) from lignes
      where INDXE=:INDX_EXERCICE AND INDX_RGP=:INDX AND SENS='C' INTO :CREDIT;
    SELECT SUM(MONTANT) from lignes
      where INDXE=:INDX_EXERCICE AND INDX_RGP=:INDX AND SENS='D' INTO :DEBIT;
    END
  if (CREDIT IS NULL) then CREDIT = 0;
  if (DEBIT IS NULL) then DEBIT = 0;
  if ((CREDIT<>0) or (DEBIT<>0) ) THEN
    BEGIN
    SOLDEC = 0;
    SOLDED = 0;
    if (SUBSTR(COMPTE,1,1)<'6') then
      BEGIN
      TOTC2 = TOTC2 + CREDIT;
      TOTD2 = TOTD2 + DEBIT;
      END
    ELSE if (SUBSTR(COMPTE,1,1)='6') then
      BEGIN
      /* Les comptes 8 sont exclus des totaux */
      CLASSE6C = CLASSE6C + CREDIT;
      CLASSE6D = CLASSE6D + DEBIT;
      END
    ELSE if (SUBSTR(COMPTE,1,1)='7') then
      BEGIN
      /* Les comptes 8 sont exclus des totaux */
      CLASSE7C = CLASSE7C + CREDIT;
      CLASSE7D = CLASSE7D + DEBIT;
      END
    IF (CREDIT>DEBIT) THEN SOLDEC = CREDIT - DEBIT;
    ELSE IF (CREDIT<DEBIT) THEN SOLDED = DEBIT - CREDIT;
    SUSPEND;
    END
  END
if (AVEC_TOTAUX=1) then
  begin
  COMPTE = NULL;
  /* Comptes de bilan */
  SOLDEC = NULL;
  SOLDED = NULL;
  INDX = -1;
  DESCRIPTION = 'Totaux comptes de bilan';
  CREDIT = TOTC2;
  DEBIT = TOTD2;
  IF (CREDIT>DEBIT) THEN SOLDEC = CREDIT - DEBIT;
  ELSE IF (CREDIT<DEBIT) THEN SOLDED = DEBIT - CREDIT;
  SUSPEND;

  /* Comptes de Gestion */
  SOLDEC = NULL;
  SOLDED = NULL;
  INDX = -2;
  DESCRIPTION = 'Totaux comptes de gestion';
  CREDIT = CLASSE6C + CLASSE7C;
  DEBIT = CLASSE6D + CLASSE7D;
  IF (CREDIT>DEBIT) THEN SOLDEC = CREDIT - DEBIT;
  ELSE IF (CREDIT<DEBIT) THEN SOLDED = DEBIT - CREDIT;
  SUSPEND;

  /* et au final */
  SOLDEC = NULL;
  SOLDED = NULL;
  INDX = -3;
  DESCRIPTION = 'Totaux de la balance';
  CREDIT = TOTC2 + CLASSE6C + CLASSE7C;
  DEBIT = TOTD2 + CLASSE6D + CLASSE7D;
  SUSPEND;

  /* Comptes 6 */
  SOLDEC = NULL;
  SOLDED = NULL;
  INDX = -4;
  DESCRIPTION = 'Totaux comptes Classe 6';
  CREDIT = CLASSE6C;
  DEBIT = CLASSE6D;
  IF (CREDIT>DEBIT) THEN SOLDEC = CREDIT - DEBIT;
  ELSE IF (CREDIT<DEBIT) THEN SOLDED = DEBIT - CREDIT;
  SUSPEND;

  /* Comptes 7 */
  SOLDEC = NULL;
  SOLDED = NULL;
  INDX = -5;
  DESCRIPTION = 'Totaux comptes Classe 7';
  CREDIT = CLASSE7C;
  DEBIT = CLASSE7D;
  IF (CREDIT>DEBIT) THEN SOLDEC = CREDIT - DEBIT;
  ELSE IF (CREDIT<DEBIT) THEN SOLDED = DEBIT - CREDIT;
  SUSPEND;

  /* RΘsultat */
  SOLDEC = NULL;
  SOLDED = NULL;
  INDX = -6;
  DESCRIPTION = 'RΘsultat de l''exercice';
  CREDIT = CLASSE7C + CLASSE6C;
  DEBIT = CLASSE7D + CLASSE6D;
  IF (CREDIT>DEBIT) THEN SOLDEC = CREDIT - DEBIT;
  ELSE IF (CREDIT<DEBIT) THEN SOLDED = DEBIT - CREDIT;
  SUSPEND;
  END

end
 ;


ALTER PROCEDURE "BALANCE_UN_COMPTE"
(
  "INDX_EXERCICE" SMALLINT,
  "INDX_CPT" INTEGER
)
RETURNS
(
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2)
)
AS
begin
SELECT SUM(MONTANT) from lignes
  where INDXE=:INDX_EXERCICE AND INDX_CPT=:INDX_CPT AND SENS='C'
  INTO :CREDIT;
SELECT SUM(MONTANT) from lignes
  where INDXE=:INDX_EXERCICE AND INDX_CPT=:INDX_CPT AND SENS='D'
  INTO :DEBIT;
if (CREDIT IS NULL) then CREDIT = 0;
if (DEBIT IS NULL) then DEBIT = 0;
SUSPEND;
end
 ;


ALTER PROCEDURE "CALCUL_VERROU_COMPTES"
AS
DECLARE VARIABLE INDX INTEGER;
BEGIN
UPDATE COMPTES SET VERROU=0;
FOR SELECT DISTINCT INDX_CPT FROM LIGNES INTO :INDX DO
    UPDATE COMPTES SET VERROU=1 WHERE INDX=:INDX;
FOR SELECT DISTINCT INDX_RGP FROM LIGNES WHERE INDX_RGP<>INDX_CPT INTO :INDX DO
    UPDATE COMPTES SET VERROU=1 WHERE INDX=:INDX;
END
 ;


ALTER PROCEDURE "CALCUL_VERROU_UN_COMPTE"
(
  "INDEX_CPT" INTEGER
)
RETURNS
(
  "VERROU" INTEGER
)
AS
BEGIN
SELECT COUNT(*) FROM LIGNES
WHERE (INDX_CPT=:INDEX_CPT) or (INDX_RGP=:INDEX_CPT) INTO :VERROU;
SUSPEND;
END
 ;


ALTER PROCEDURE "CHECK_GENPK"
RETURNS
(
  "INFO" VARCHAR(50)
)
AS
DECLARE VARIABLE N1 INTEGER;
DECLARE VARIABLE N2 INTEGER;
begin
/* GENPK_BLOBS */
N1 = gen_id(genpk_blobs,0);
SELECT MAX(INDX) FROM blobs INTO :N2;
if (n1<>n2 AND n1>5) then
  begin
  info = 'Index de la table BLOBS rΘajustΘ de ' || n1 || ' α ' || n2;
  suspend;
  n1 = gen_id(genpk_blobs, n2-n1);
  end

/* GENPK_COMPTES */
N1 = gen_id(genpk_comptes,0);
SELECT MAX(INDX) FROM comptes INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table COMPTES rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_comptes, n2-n1);
  suspend;
  end

/* GENPK_ETATS */
N1 = gen_id(genpk_etats,0);
SELECT MAX(INDX) FROM etats INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table ETATS rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_etats, n2-n1);
  suspend;
  end

/* GENPK_EXERCICE */
N1 = gen_id(genpk_exercice,0);
SELECT MAX(INDX) FROM exercice INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table EXERCICE rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_exercice, n2-n1);
  suspend;
  end

/* GENPK_JOURNAL */
N1 = gen_id(genpk_journal,0);
SELECT MAX(INDX) FROM journal INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table JOURNAL rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_journal, n2-n1);
  suspend;
  end

/* GENPK_JOURNAL_MOIS */
N1 = gen_id(genpk_journal_mois,0);
SELECT MAX(INDX) FROM journal_mois INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table JOURNAL_MOIS rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_journal_mois, n2-n1);
  suspend;
  end

/* GENPK_MODELES */
N1 = gen_id(genpk_modeles,0);
SELECT MAX(INDX) FROM MODELES INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table MODELES rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_modeles, n2-n1);
  suspend;
  end

/* GENPK_MOUVEMENTS */
N1 = gen_id(genpk_mouvements,0);
SELECT MAX(INDX) FROM mouvements INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table MOUVEMENTS rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_mouvements, n2-n1);
  suspend;
  end

/* GENPK_TIERS */
N1 = gen_id(genpk_tiers,0);
SELECT MAX(INDX) FROM tiers INTO :N2;
if (n1<>n2 AND n1>1) then
  begin
  info = 'Index de la table TIERS rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_tiers, n2-n1);
  suspend;
  end

/* GENPK_TVA */
N1 = gen_id(genpk_tva,0);
SELECT MAX(INDX) FROM TVA INTO :N2;
if (n1<>n2) then
  begin
  info = 'Index de la table TVA rΘajustΘ de ' || n1 || ' α ' || n2;
  n1 = gen_id(genpk_tva, n2-n1);
  suspend;
  end
end
 ;


ALTER PROCEDURE "COPIE_LETTRAGE"
(
  "MVT_1" INTEGER
)
RETURNS
(
  "INFO" VARCHAR(80)
)
AS
DECLARE VARIABLE INDX_CPT INTEGER;
DECLARE VARIABLE NOLIGNE SMALLINT;
DECLARE VARIABLE LIBELLE VARCHAR(80);
DECLARE VARIABLE MONTANT DECIMAL(12,2);
DECLARE VARIABLE SENS CHAR(1);
DECLARE VARIABLE LETTRAGE VARCHAR(4);
DECLARE VARIABLE NOLIGNE1 SMALLINT;
DECLARE VARIABLE INDX_E INTEGER;
DECLARE VARIABLE MVT_2 INTEGER;
begin
SELECT INDX_EXERCICE FROM MOUVEMENTS WHERE INDX=:MVT_1 INTO :INDX_E;
SELECT MVT_A_NOUVEAU FROM EXERCICE WHERE INDX=:INDX_E INTO :MVT_2;
if ((MVT_2 IS NOT NULL) AND (MVT_2>0)) then
  BEGIN
  FOR SELECT INDX_CPT,LIGNE,LIBELLE,MONTANT,SENS,LETTRAGE  FROM LIGNES
      WHERE INDX_MVT=:MVT_2 AND LETTRAGE IS NOT NULL
      INTO :INDX_CPT,:NOLIGNE,:LIBELLE,:MONTANT,:SENS,:LETTRAGE DO
    BEGIN
    /* existe t'il une ligne identique dans le nouveau Mvt */
    SELECT LIGNE FROM LIGNES
      WHERE INDX_MVT=:MVT_1 AND INDX_CPT=:INDX_CPT AND LIBELLE=:LIBELLE AND MONTANT=:MONTANT AND SENS=:SENS
      INTO :NOLIGNE1;
    if ((NOLIGNE1 IS NOT NULL) AND (NOLIGNE1>0)) then
      BEGIN
      /* Copier le lettrage */
      UPDATE LIGNES SET LETTRAGE=:LETTRAGE WHERE INDX_MVT=:MVT_1 AND LIGNE=:NOLIGNE1;
      UPDATE LIGNES SET LETTRAGE=NULL WHERE INDX_MVT=:MVT_2 AND LIGNE=:NOLIGNE;
      END
    ELSE
      BEGIN
      UPDATE LIGNES SET LETTRAGE=NULL WHERE LETTRAGE=:LETTRAGE AND INDXE=:INDX_E;
      INFO = 'Le lettrage ' || LETTRAGE || ' a ΘtΘ effacΘ';
      SUSPEND;
      END
    END
  END
DELETE FROM MOUVEMENTS WHERE INDX=:MVT_2;
UPDATE EXERCICE SET MVT_A_NOUVEAU=:MVT_1 WHERE INDX=:INDX_E;
end
 ;


ALTER PROCEDURE "EFFACE_MODELE"
(
  "INDEX_MODELE" SMALLINT
)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
SELECT INDX_BLOB FROM MODELES WHERE INDX=:INDEX_MODELE INTO :I;
DELETE FROM MODELES WHERE INDX=:INDEX_MODELE;
DELETE FROM BLOBS WHERE INDX=:I;
END
 ;


ALTER PROCEDURE "EFF_COMPTE"
(
  "INDEX_CPT" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
BEGIN
SELECT EFF_OK FROM EFF_COMPTE_OK(:INDEX_CPT) INTO :EFF_OK;
IF (EFF_OK=1) THEN DELETE FROM COMPTES WHERE INDX=:INDEX_CPT OR REGROUPEMENT=:INDEX_CPT;
EXIT;
END
 ;


ALTER PROCEDURE "EFF_COMPTE_OK"
(
  "INDEX_CPT" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE INDX INTEGER;
BEGIN
SELECT VERROU FROM CALCUL_VERROU_UN_COMPTE(:INDEX_CPT) INTO :N;
IF (N=0) THEN EFF_OK=1; ELSE EFF_OK=0;
SUSPEND;
END
 ;


ALTER PROCEDURE "EFF_EXERCICE_OK"
(
  "INDEX_E" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT COUNT(*) FROM MOUVEMENTS M
JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M AND JM.INDX_EXERCICE=:INDEX_E
WHERE VERROU<>0 INTO :N;
IF (N>0) THEN EFF_OK=0; ELSE EFF_OK=1;
SUSPEND;
END
 ;


ALTER PROCEDURE "EFF_JOURNAL_MOIS"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT COUNT(*) FROM MOUVEMENTS WHERE VERROU<>0 AND INDX_JOURNAL_M=:INDEX_JM INTO :N;
IF (N>0) THEN EFF_OK=0; ELSE
  BEGIN
  EFF_OK=1;
  DELETE FROM JOURNAL_MOIS WHERE INDX=:INDEX_JM;
  END
SUSPEND;
END
 ;


ALTER PROCEDURE "EFF_JOURNAL_MOIS_OK"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT COUNT(*) FROM MOUVEMENTS WHERE VERROU<>0 AND INDX_JOURNAL_M=:INDEX_JM INTO :N;
IF (N>0) THEN EFF_OK=0; ELSE EFF_OK=1;
SUSPEND;
END
 ;


ALTER PROCEDURE "EFF_JOURNAL_OK"
(
  "INDEX_J" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT COUNT(*) FROM MOUVEMENTS M
JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M AND JM.INDX_JOURNAL=:INDEX_J
WHERE VERROU<>0 INTO :N;
IF (N>0) THEN EFF_OK=0; ELSE EFF_OK=1;
SUSPEND;
END
 ;


ALTER PROCEDURE "EFF_LISTE_COMPTE_OK"
(
  "NOLISTE" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT COUNT(LI.INDX) FROM LISTE LI
JOIN LIGNES LG ON LG.INDX_CPT=LI.INDX OR LG.INDX_RGP=LI.INDX
JOIN MOUVEMENTS M ON M.INDX=LG.INDX_MVT AND M.VERROU=1
WHERE LI.NOLISTE=:NOLISTE
INTO N;
IF (N>0) THEN EFF_OK=0; ELSE EFF_OK=1;
SUSPEND;
END
 ;


ALTER PROCEDURE "EFF_MOUVEMENT"
(
  "INDEX_MVT" INTEGER
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT VERROU FROM MOUVEMENTS WHERE INDX=:INDEX_MVT INTO :N;
if (N=0) then
  SELECT COUNT(*) FROM LIGNES WHERE INDX_MVT=:INDEX_MVT AND LETTRAGE IS NOT NULL INTO :N;
IF (N<>0) THEN EFF_OK=0; ELSE
  BEGIN
  EFF_OK=1;
  DELETE FROM MOUVEMENTS WHERE INDX=:INDEX_MVT;
  END
SUSPEND;
END
 ;


ALTER PROCEDURE "EXPORT_ADRESSE"
(
  "INDX" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(30)
)
AS
DECLARE VARIABLE ADRESSE VARCHAR(26);
DECLARE VARIABLE ADRESSE2 VARCHAR(26);
DECLARE VARIABLE LOCALITE VARCHAR(21);
DECLARE VARIABLE CODEPOSTAL VARCHAR(8);
DECLARE VARIABLE VILLE VARCHAR(22);
DECLARE VARIABLE PAYS VARCHAR(22);
BEGIN
SELECT ADRESSE,ADRESSE2,LOCALITE,CODEPOSTAL,VILLE,PAYS
  FROM TIERS
  WHERE INDX=:INDX
  INTO :ADRESSE,:ADRESSE2,:LOCALITE,:CODEPOSTAL,:VILLE,:PAYS;

RESULT = '[ADRESSE' || :INDX || ']';
SUSPEND;
RESULT = ADRESSE;
SUSPEND;
RESULT = ADRESSE2;
SUSPEND;
RESULT = LOCALITE;
SUSPEND;
RESULT = CODEPOSTAL;
SUSPEND;
RESULT = VILLE;
SUSPEND;
RESULT = PAYS;
SUSPEND;
END
 ;


ALTER PROCEDURE "EXPORT_COMPTE"
(
  "INDX_C" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(90)
)
AS
DECLARE VARIABLE INDX INTEGER;
DECLARE VARIABLE LIB_COURT VARCHAR(15);
DECLARE VARIABLE LIB_LONG VARCHAR(80);
DECLARE VARIABLE INDX_TIERS INTEGER;
DECLARE VARIABLE REGROUPEMENT VARCHAR(15);
DECLARE VARIABLE TVA DECIMAL(4,2);
BEGIN
SELECT C.LIB_COURT,C.LIB_LONG,C.INDX_TIERS,C1.LIB_COURT,T.VALEUR FROM COMPTES C
LEFT JOIN COMPTES C1 ON C1.INDX=C.REGROUPEMENT
LEFT JOIN TVA T ON T.INDX=C.INDX_TVA
WHERE C.INDX=:INDX_C
INTO :LIB_COURT,:LIB_LONG,:INDX_TIERS,:REGROUPEMENT,:TVA;
RESULT = '[COMPTE]';
SUSPEND;
RESULT = LIB_COURT;
SUSPEND;
RESULT = LIB_LONG;
SUSPEND;
RESULT = INDX_TIERS;
SUSPEND;
RESULT = REGROUPEMENT;
SUSPEND;
RESULT = TVA;
SUSPEND;
END
 ;


ALTER PROCEDURE "EXPORT_COMPTES"
(
  "NOLISTE" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(250)
)
AS
DECLARE VARIABLE INDX INTEGER;
DECLARE VARIABLE INDX1 INTEGER;
DECLARE VARIABLE INDX2 INTEGER;
DECLARE VARIABLE LIB_COURT VARCHAR(15);
DECLARE VARIABLE LIB_LONG VARCHAR(80);
DECLARE VARIABLE CODEMAGASIN VARCHAR(15);
DECLARE VARIABLE REGROUPEMENT VARCHAR(15);
DECLARE VARIABLE VALEUR_TVA VARCHAR(6);
BEGIN
/* L'exportation de comptes n'a de sens que vers une autre compta
Il faut donc exporter TOUT ce qui dΘpend des comptes.
La liste des comptes α exporter est dans la table LISTE selectionnΘ par NOLISTE */

/* D'abord les TVA */
FOR SELECT DISTINCT C.INDX_TVA FROM LISTE L
    JOIN COMPTES C ON C.INDX=L.INDX
    WHERE L.NOLISTE=:NOLISTE
    INTO :INDX
    DO IF (INDX IS NOT NULL) THEN FOR SELECT RESULT FROM EXPORT_TVA(:INDX) INTO :RESULT DO SUSPEND;

/* Puis les tiers */

/* Puis les comptes de regroupement */
FOR SELECT DISTINCT C.REGROUPEMENT FROM LISTE L
    JOIN COMPTES C ON C.INDX=L.INDX
    WHERE L.NOLISTE=:NOLISTE AND C.REGROUPEMENT IS NOT NULL
                    INTO :INDX1 DO
    BEGIN
    SELECT COUNT(*) FROM LISTE WHERE INDX=:INDX1 AND NOLISTE=:NOLISTE INTO :INDX2;
    IF (INDX2=0) THEN FOR SELECT RESULT FROM EXPORT_COMPTE(:INDX1) INTO :RESULT DO SUSPEND;
    END

/* Marquer la fin des comptes de regroupement */
RESULT = '[FIN PREAMBULE]';
SUSPEND;

/* Enfin les comptes */
FOR SELECT DISTINCT INDX FROM LISTE  WHERE NOLISTE=:NOLISTE INTO :INDX1 DO
  FOR SELECT RESULT FROM EXPORT_COMPTE(:INDX1) INTO :RESULT DO SUSPEND;

/* Et faire le mΘnage */
DELETE FROM LISTE WHERE NOLISTE=:NOLISTE;
END
 ;


ALTER PROCEDURE "EXPORT_TIERS"
(
  "INDX" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(250)
)
AS
BEGIN
RESULT=NULL;
SUSPEND;
END
 ;


ALTER PROCEDURE "EXPORT_TVA"
(
  "INDX" INTEGER
)
RETURNS
(
  "RESULT" VARCHAR(250)
)
AS
DECLARE VARIABLE DESIGNATION VARCHAR(40);
DECLARE VARIABLE VALEUR VARCHAR(8);
BEGIN
SELECT DESIGNATION,VALEUR FROM TVA WHERE INDX=:INDX INTO :DESIGNATION,:VALEUR;
IF (DESIGNATION IS NOT NULL) THEN
  BEGIN
  RESULT = '[TVA]';
  SUSPEND;
  RESULT = DESIGNATION;
  SUSPEND;
  RESULT = VALEUR;
  SUSPEND;
  END
END
 ;


ALTER PROCEDURE "EXTRAIT_COMPTE"
(
  "INDX_CPT" INTEGER,
  "INDX_EXERCICE" SMALLINT,
  "LETTRE" SMALLINT,
  "NON_LETTRE" SMALLINT
)
RETURNS
(
  "DATE_MVT" DATE,
  "CODEJ" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "SOLDEP" DECIMAL(12, 2)
)
AS
DECLARE VARIABLE SENS CHAR(10);
DECLARE VARIABLE MONTANT DECIMAL(12,2);
begin
  SOLDEP = 0;
  FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE
            ,M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE
            ,J.SHORTDESI
            from lignes L
    JOIN MOUVEMENTS M ON M.INDX=L.INDX_MVT
    JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
    JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
    /* On ne regroupe pas les comptes */
    where (INDXE=:INDX_EXERCICE) AND (L.INDX_CPT=:INDX_CPT)
    AND (L.MONTANT IS NOT NULL) AND (L.MONTANT<>0)
    ORDER BY M.DATE_MVT, J.SHORTDESI
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,:DATE_MVT,:NO_INTERNE,:NO_EXTERNE
    ,:CODEJ DO
    BEGIN
    IF (SENS='C') THEN
      BEGIN
      SOLDEP = SOLDEP - MONTANT;
      CREDIT = MONTANT;
      DEBIT = NULL;
      END
    ELSE
     BEGIN
     SOLDEP = SOLDEP + MONTANT;
     CREDIT=NULL;
     DEBIT = MONTANT;
     END
    SUSPEND;
    END
end
 ;


ALTER PROCEDURE "GET_INDEXS_COMPTE"
(
  "LIB_CG" VARCHAR(15),
  "LIB_CA" VARCHAR(15)
)
RETURNS
(
  "INDX_CG" VARCHAR(15),
  "INDX_CA" VARCHAR(15)
)
AS
BEGIN

IF (LIB_CA IS NULL or (LIB_CA='')) THEN
  BEGIN
  SELECT INDX FROM COMPTES WHERE upper(LIB_COURT)=upper(:LIB_CG) AND REGROUPEMENT IS NULL INTO :INDX_CG;
  END
ELSE
  SELECT C.REGROUPEMENT,C.INDX FROM COMPTES C
  JOIN COMPTES C1 ON C1.INDX=C.REGROUPEMENT AND upper(C1.LIB_COURT)=upper(:LIB_CG)
  WHERE upper(C.LIB_COURT)=upper(:LIB_CA) INTO :INDX_CG,:INDX_CA;
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_INDEX_JM"
(
  "DESI" VARCHAR(4),
  "MOIS" SMALLINT,
  "INDX_EXERCICE" SMALLINT
)
RETURNS
(
  "INDX" INTEGER
)
AS
BEGIN
SELECT JM.INDX FROM JOURNAL_MOIS JM
JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL AND J.SHORTDESI=:DESI
WHERE JM.INDX_EXERCICE=:INDX_EXERCICE AND JM.MOIS=:MOIS
INTO :INDX;
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_INDX_COMPTE"
(
  "LIB_CG" VARCHAR(15),
  "LIB_CA" VARCHAR(15)
)
RETURNS
(
  "INDX" INTEGER
)
AS
BEGIN
IF (LIB_CA IS NULL or (LIB_CA='')) THEN SELECT INDX FROM COMPTES WHERE upper(LIB_COURT)=upper(:LIB_CG) AND REGROUPEMENT IS NULL INTO :INDX;
ELSE
  SELECT C.INDX FROM COMPTES C
  JOIN COMPTES C1 ON C1.INDX=C.REGROUPEMENT AND upper(C1.LIB_COURT)=upper(:LIB_CG)
  WHERE upper(C.LIB_COURT)=upper(:LIB_CA) INTO :INDX;
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_LETTRAGE"
(
  "INDX_CPT" INTEGER,
  "INDX_E" INTEGER
)
RETURNS
(
  "LETTRAGE" VARCHAR(4)
)
AS
DECLARE VARIABLE I INTEGER;
begin
LETTRAGE = 'A';
I = 1;
WHILE (i<>0) DO
  BEGIN
  SELECT COUNT(*) FROM LIGNES L
  WHERE INDXE=:INDX_E AND INDX_CPT=:INDX_CPT AND LETTRAGE=:LETTRAGE
  INTO :i;
  if (i<>0) then SELECT RESULT FROM get_next_lettrage(:lettrage) INTO :lettrage;
  END
suspend;
end
 ;


ALTER PROCEDURE "GET_LIB_COURTS"
(
  "INDX" INTEGER
)
RETURNS
(
  "LIBGENE" VARCHAR(15),
  "LIBAUXI" VARCHAR(15)
)
AS
DECLARE VARIABLE AUX INTEGER;
BEGIN
SELECT "LIB_COURT","REGROUPEMENT" FROM COMPTES WHERE INDX=:INDX INTO :LIBGENE,:AUX;
IF (AUX IS NOT NULL) THEN
  BEGIN
  LIBAUXI = LIBGENE;
  SELECT LIB_COURT FROM COMPTES WHERE INDX=:AUX INTO :LIBGENE;
  END
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_NEXT_LETTRAGE"
(
  "LETTRAGE" VARCHAR(4)
)
RETURNS
(
  "RESULT" VARCHAR(4)
)
AS
DECLARE VARIABLE LEN SMALLINT;
DECLARE VARIABLE I SMALLINT;
BEGIN
LEN = STRLEN(LETTRAGE);
if (LETTRAGE=SUBSTRLEN('ZZZZ',1,LEN)) then
  BEGIN
  if (LEN<4) then RESULT = SUBSTRLEN('AAAA',1,LEN+1);
  ELSE EXCEPTION OUT_OF_LETTRES;
  END
ELSE
  BEGIN
  i = LEN;
  WHILE ((i>1) AND (SUBSTRLEN(LETTRAGE,i,1)='Z')) DO i=i-1;
  RESULT = SUBSTRLEN(LETTRAGE,1,i-1)
        || ASCII_CHAR(ascii_val(SUBSTRLEN(LETTRAGE,i,1))+1)
        || SUBSTR('AAAA',i+1,LEN);
  END
suspend;
END
 ;


ALTER PROCEDURE "GET_NOM_COMPTE"
(
  "INDX" INTEGER
)
RETURNS
(
  "NOM" VARCHAR(250)
)
AS
DECLARE VARIABLE AUX INTEGER;
DECLARE VARIABLE LCG VARCHAR(15);
BEGIN
SELECT "LIB_COURT" || ' ' || "LIB_LONG","REGROUPEMENT" FROM COMPTES WHERE INDX=:INDX INTO :NOM,:AUX;
IF (AUX IS NOT NULL) THEN
  BEGIN
  SELECT LIB_COURT FROM COMPTES WHERE INDX=:AUX INTO :LCG;
  NOM = LCG || ' ' || NOM;
  END
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_NOM_COMPTE_GENERAL"
(
  "INDX" INTEGER
)
RETURNS
(
  "LIBELLE" VARCHAR(15)
)
AS
DECLARE VARIABLE AUX INTEGER;
BEGIN
SELECT "LIB_COURT","REGROUPEMENT" FROM COMPTES WHERE INDX=:INDX INTO :LIBELLE,:AUX;
IF (AUX IS NOT NULL) THEN SELECT LIB_COURT FROM COMPTES WHERE INDX=:AUX INTO :LIBELLE;
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_REPORT_JM_COMPTE"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
DECLARE VARIABLE INDX_EXERCICE SMALLINT;
DECLARE VARIABLE INDX_JOURNAL SMALLINT;
DECLARE VARIABLE TYPEJ SMALLINT;
DECLARE VARIABLE NOMOIS SMALLINT;
DECLARE VARIABLE C1 DECIMAL(12,2);
DECLARE VARIABLE INDX_C INTEGER;
BEGIN
/* Rapatrier diverses variables */
  SELECT JM.INDX_EXERCICE,JM.INDX_JOURNAL,JM.NOMOIS,J.TYPEJX,J.INDX_COMPTE FROM JOURNAL_MOIS JM
  JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
  WHERE JM.INDX=:INDEX_JM
  INTO :INDX_EXERCICE,:INDX_JOURNAL,:NOMOIS,:TYPEJ,:INDX_C;
/* Puis le solde des journaux antΘrieurs */
  IF (TYPEJ=4) THEN
    BEGIN
    CREDIT=0;
    DEBIT=0;
    END
  ELSE
    BEGIN
    /* Calculer solde initial */
    SELECT DEBIT,CREDIT FROM A_NOUVEAU_COMPTE(:INDX_EXERCICE,:INDX_C) INTO :DEBIT,:CREDIT;

    SELECT SUM(SOLDE_COMPTE) FROM JOURNAL_MOIS
    WHERE INDX_JOURNAL=:INDX_JOURNAL AND INDX_EXERCICE=:INDX_EXERCICE AND NOMOIS<:NOMOIS
    INTO :C1;
    IF (C1 IS NOT NULL) THEN IF (C1<0) THEN DEBIT = DEBIT - C1; ELSE CREDIT = CREDIT + C1;
    END
SUSPEND;
END
 ;


ALTER PROCEDURE "GET_SOLDE_JOURNAL"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "MONTANT" DECIMAL(12, 2)
)
AS
BEGIN
   SELECT SUM(L.MONTANT) FROM MOUVEMENTS M
   JOIN  LIGNES L ON L.INDX_MVT=M.INDX AND L.SENS='C'
   WHERE M.INDX_JOURNAL_M=:INDEX_JM
   INTO :MONTANT;
SUSPEND;
END
 ;


ALTER PROCEDURE "GRAND_LIVRE"
(
  "INDX_EXERCICE" SMALLINT,
  "INDEX_CPT" INTEGER,
  "AVEC_TIERS" SMALLINT,
  "QUE_LETTRE" SMALLINT,
  "QUE_NON_LETTRE" SMALLINT
)
RETURNS
(
  "INDX" INTEGER,
  "DATE_MVT" DATE,
  "CODEJ" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "SOLDEP" DECIMAL(12, 2),
  "LIB_GENE" VARCHAR(15),
  "LIB_AUXI" VARCHAR(15)
)
AS
DECLARE VARIABLE SENS CHAR(10);
DECLARE VARIABLE MONTANT DECIMAL(12,2);
begin
FOR SELECT INDX,COMPTE,TIERS FROM LISTE_COMPTES(:AVEC_TIERS)
INTO :INDX,:LIB_GENE,:LIB_AUXI DO IF ((:INDEX_CPT=0) or (:INDEX_CPT=:INDX)) THEN
  BEGIN
  SOLDEP = 0;
  FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE
            ,M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE
            ,J.SHORTDESI
            from lignes L
    JOIN MOUVEMENTS M ON M.INDX=L.INDX_MVT
    JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
    JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
    where ((INDXE=:INDX_EXERCICE) AND (INDX_CPT=:INDX) or ((:AVEC_TIERS=0) AND (INDX_RGP=:INDX)))
          AND ((:QUE_LETTRE=0) or (L.LETTRAGE IS NOT NULL))
          AND ((:QUE_NON_LETTRE=0) or (L.LETTRAGE IS NULL))
          AND ((MONTANT IS NOT NULL) AND (MONTANT<>0))
    ORDER BY M.DATE_MVT, J.SHORTDESI
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,:DATE_MVT,:NO_INTERNE,:NO_EXTERNE
    ,:CODEJ DO
    BEGIN
    IF (SENS='C') THEN
      BEGIN
      SOLDEP = SOLDEP - MONTANT;
      CREDIT = MONTANT;
      DEBIT = NULL;
      END
    ELSE
      BEGIN
      SOLDEP = SOLDEP + MONTANT;
      CREDIT=NULL;
      DEBIT = MONTANT;
      END
    SUSPEND;
    END
  END
end
 ;


ALTER PROCEDURE "IMPORT_COMPTE"
(
  "LIB_COURT" VARCHAR(15),
  "LIB_LONG" VARCHAR(80),
  "CODEMAGASIN" VARCHAR(15),
  "REGROUPEMENT" VARCHAR(15),
  "VALEUR_TVA" VARCHAR(6)
)
AS
DECLARE VARIABLE "INDX" INTEGER;
DECLARE VARIABLE "INDX_TIERS"      INTEGER;       /* Client ou fournisseur */
DECLARE VARIABLE "INDX_REGROUPE"  INTEGER;       /* Compte principal: 411DUPONT est attachΘ α 411000 */
DECLARE VARIABLE "INDX_TVA"       INTEGER;
BEGIN

/* tiers */
IF (CODEMAGASIN<>'') THEN
  BEGIN
  SELECT MAX(INDX) FROM TIERS WHERE CODEMAGASIN=:CODEMAGASIN INTO :INDX_TIERS;
  IF (INDX_TIERS IS NULL) THEN /* INSERER */
    BEGIN
    SELECT GEN_ID("GENPK_TIERS",1) FROM RDB$DATABASE INTO :INDX_TIERS;
    INSERT INTO TIERS (INDX,CODEMAGASIN,NOM) VALUES (:INDX_TIERS,:CODEMAGASIN,'CREE PAR IMPORT_COMPTE');
    END
  END
ELSE INDX_TIERS = NULL;

/* AUXILLIAIRES */
IF (REGROUPEMENT<>'') THEN
  BEGIN
  SELECT MAX(INDX) FROM COMPTES WHERE LIB_COURT=:REGROUPEMENT INTO :INDX_REGROUPE;
  IF (INDX_REGROUPE IS NULL) THEN
    BEGIN
    SELECT GEN_ID("GENPK_COMPTES",1) FROM RDB$DATABASE INTO :INDX_REGROUPE;
    INSERT INTO COMPTES (INDX,LIB_COURT,LIB_LONG) VALUES (:INDX_REGROUPE,:REGROUPEMENT,'CREE PAR IMPORT_COMPTE '|| :LIB_COURT);
    END
  END
ELSE INDX_REGROUPE = NULL;

/* TVA */
IF (VALEUR_TVA<>'') THEN
  BEGIN
  SELECT MAX(INDX) FROM TVA WHERE VALEUR=:VALEUR_TVA INTO :INDX_TVA;
  IF (INDX_TVA IS NULL) THEN
    BEGIN
    SELECT GEN_ID("GENPK_TVA",1) FROM RDB$DATABASE INTO :INDX_TVA;
    INSERT INTO TVA (INDX,DESIGNATION,VALEUR) VALUES (:INDX_TVA,'CREE PAR IMPORT_COMPTE '|| :LIB_COURT,:VALEUR_TVA);
    END
  END
ELSE INDX_TVA = NULL;

/* LE COMPTE */
SELECT MAX(INDX) FROM COMPTES WHERE LIB_COURT=:LIB_COURT INTO :INDX;
IF (INDX IS NULL) THEN
  BEGIN
  SELECT GEN_ID("GENPK_COMPTES",1) FROM RDB$DATABASE INTO :INDX;
  INSERT INTO COMPTES (INDX,LIB_COURT,LIB_LONG,INDX_TIERS,REGROUPEMENT,INDX_TVA) VALUES (:INDX,:LIB_COURT,:LIB_LONG,:INDX_TIERS,:INDX_REGROUPE,:INDX_TVA);
  END
ELSE UPDATE COMPTES SET LIB_LONG=:LIB_LONG,INDX_TIERS=:INDX_TIERS,REGROUPEMENT=:INDX_REGROUPE,INDX_TVA=:INDX_TVA WHERE INDX=:INDX;


END
 ;


ALTER PROCEDURE "IMPORT_TVA"
(
  "DESIGNATION" VARCHAR(40),
  "VALEUR" DECIMAL(4, 2)
)
AS
DECLARE VARIABLE INDX SMALLINT;
DECLARE VARIABLE OLD_DESIGNATION VARCHAR(40);
BEGIN

/* TVA */
SELECT INDX,DESIGNATION FROM TVA WHERE VALEUR=:VALEUR INTO :INDX,:OLD_DESIGNATION;
IF (INDX IS NULL) THEN
    BEGIN
    SELECT GEN_ID("GENPK_TVA",1) FROM RDB$DATABASE INTO :INDX;
    INSERT INTO TVA (INDX,DESIGNATION,VALEUR) VALUES (:INDX,:DESIGNATION,:VALEUR);
    END
ELSE IF ((OLD_DESIGNATION IS NULL) OR (OLD_DESIGNATION<>DESIGNATION)) THEN
  UPDATE TVA SET DESIGNATION=:DESIGNATION WHERE INDX=:INDX;
END
 ;


ALTER PROCEDURE "INTERROGATION"
(
  "INDEX_EXERCICE" INTEGER,
  "DATEDEB" DATE,
  "DATEFIN" DATE,
  "DETAILS" SMALLINT
)
RETURNS
(
  "LIBJOURNAL" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "DATE_MVT" DATE,
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "LIB_CPT" VARCHAR(80),
  "INDX_JL" SMALLINT,
  "DATEPONT" DATE
)
AS
DECLARE VARIABLE SENS CHAR(1);
DECLARE VARIABLE LONGAUXI VARCHAR(80);
BEGIN
FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE,L.LIGNE,
M.INDX,M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE,M.VERROU,M.DATE_PONT,
C.LIB_COURT,C1.LIB_COURT,C.LIB_LONG,C1.LIB_LONG,
J.SHORTDESI,J.INDX
from MOUVEMENTS M
    JOIN LIGNES L ON L.INDX_MVT=M.INDX AND ((:DETAILS=1) or (L.LIGNE=1))
    JOIN COMPTES C ON C.INDX=L.INDX_RGP
    JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
    JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
    LEFT JOIN COMPTES C1 ON C1.INDX=L.INDX_CPT AND L.INDX_CPT<>L.INDX_RGP
    where M.INDX_EXERCICE=:INDEX_EXERCICE AND DATE_MVT>=:DATEDEB AND DATE_MVT<=:DATEFIN
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,:NOLIGNE,
         :INDX_MVT,:DATE_MVT,:NO_INTERNE,:NO_EXTERNE,:VERROU,:DATEPONT,
         :GENE,:AUXI,:LIB_CPT,:LONGAUXI,:LIBJOURNAL,:indx_jl
         DO
  BEGIN
  IF (SENS='C') THEN
    BEGIN
    CREDIT = MONTANT;
    DEBIT = NULL;
    END
  ELSE
    BEGIN
    CREDIT=NULL;
    DEBIT = MONTANT;
    END
  IF (AUXI<>'') THEN LIB_CPT=LONGAUXI;
  SUSPEND;
  END
END
 ;


ALTER PROCEDURE "INTERROGATION_CPT"
(
  "INDX" INTEGER,
  "INDX_EXERCICE" SMALLINT
)
RETURNS
(
  "CODEJ" VARCHAR(4),
  "DATE_MVT" DATE,
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "INDX_CPT" INTEGER
)
AS
DECLARE VARIABLE SENS CHAR(1);
BEGIN
FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE,L.INDX_MVT,L.LIGNE,L.INDX_CPT,
           M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE,
           J.SHORTDESI,C.LIB_COURT,M.VERROU from lignes L
    JOIN MOUVEMENTS M ON M.INDX_EXERCICE=:INDX_EXERCICE AND M.INDX=L.INDX_MVT
    JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
    JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
    LEFT JOIN COMPTES C ON C.INDX=L.INDX_CPT AND L.INDX_CPT<>L.INDX_RGP
    where (L.INDX_CPT=:INDX) or (L.INDX_RGP=:INDX)
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,:INDX_MVT,:NOLIGNE,:INDX_CPT,
         :DATE_MVT,:NO_INTERNE,:NO_EXTERNE,:CODEJ,:AUXI,:VERROU DO
  BEGIN
  IF (SENS='C') THEN
    BEGIN
    CREDIT = MONTANT;
    DEBIT = NULL;
    END
  ELSE
    BEGIN
    CREDIT=NULL;
    DEBIT = MONTANT;
    END
  SUSPEND;
  END
END
 ;


ALTER PROCEDURE "INTERROGATION_JL"
(
  "INDX_JM" INTEGER
)
RETURNS
(
  "NO_INTERNE" VARCHAR(15),
  "DATE_MVT" DATE,
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "LIB_CPT" VARCHAR(80)
)
AS
DECLARE VARIABLE SENS CHAR(1);
DECLARE VARIABLE LONGAUXI VARCHAR(80);
BEGIN
FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE,L.INDX_MVT,L.LIGNE,
M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE,M.VERROU,
C.LIB_COURT,C1.LIB_COURT,C.LIB_LONG,C1.LIB_LONG
from MOUVEMENTS M
    JOIN LIGNES L ON L.INDX_MVT=M.INDX
    JOIN COMPTES C ON C.INDX=L.INDX_RGP
    LEFT JOIN COMPTES C1 ON C1.INDX=L.INDX_CPT AND L.INDX_CPT<>L.INDX_RGP
    where M.INDX_JOURNAL_M=:INDX_JM
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,:INDX_MVT,:NOLIGNE,
         :DATE_MVT,:NO_INTERNE,:NO_EXTERNE,:VERROU,
         :GENE,:AUXI,:LIB_CPT,:LONGAUXI DO
  BEGIN
  IF (SENS='C') THEN
    BEGIN
    CREDIT = MONTANT;
    DEBIT = NULL;
    END
  ELSE
    BEGIN
    CREDIT=NULL;
    DEBIT = MONTANT;
    END
  IF (AUXI<>'') THEN LIB_CPT=LONGAUXI;
  SUSPEND;
  END
END
 ;


ALTER PROCEDURE "INTERROGATION_LISTE"
(
  "NOLISTE" INTEGER
)
RETURNS
(
  "LIBJOURNAL" VARCHAR(4),
  "NO_INTERNE" VARCHAR(15),
  "DATE_MVT" DATE,
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2),
  "INDX_MVT" INTEGER,
  "NOLIGNE" SMALLINT,
  "LIB_CPT" VARCHAR(80),
  "INDX_JL" SMALLINT,
  "DATEPONT" DATE
)
AS
DECLARE VARIABLE SENS CHAR(1);
DECLARE VARIABLE LONGAUXI VARCHAR(80);
BEGIN
FOR SELECT INDX,NOLIGNE FROM LISTE WHERE NOLISTE=:NOLISTE INTO :INDX_MVT,:NOLIGNE DO
  BEGIN
  FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE,
  M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE,M.VERROU,M.DATE_PONT,
  C.LIB_COURT,C1.LIB_COURT,C.LIB_LONG,C1.LIB_LONG,
  J.SHORTDESI,J.INDX
  FROM LIGNES L
    JOIN MOUVEMENTS M ON M.INDX=:INDX_MVT
    JOIN COMPTES C ON C.INDX=L.INDX_RGP
    JOIN JOURNAL_MOIS JM ON JM.INDX=M.INDX_JOURNAL_M
    JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
    LEFT JOIN COMPTES C1 ON C1.INDX=L.INDX_CPT AND L.INDX_CPT<>L.INDX_RGP
    where L.INDX_MVT=:INDX_MVT AND L.LIGNE=:NOLIGNE
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,
         :DATE_MVT,:NO_INTERNE,:NO_EXTERNE,:VERROU,:DATEPONT,
         :GENE,:AUXI,:LIB_CPT,:LONGAUXI,:LIBJOURNAL,:indx_jl
         DO
    BEGIN
    IF (SENS='C') THEN
      BEGIN
      CREDIT = MONTANT;
      DEBIT = NULL;
      END
    ELSE
      BEGIN
      CREDIT=NULL;
      DEBIT = MONTANT;
      END
    IF (AUXI<>'') THEN LIB_CPT=LONGAUXI;
    SUSPEND;
    END
  END
END
 ;


ALTER PROCEDURE "INTERROGATION_MVT"
(
  "INDX_MVT" INTEGER
)
RETURNS
(
  "NOLIGNE" SMALLINT,
  "DATE_MVT" DATE,
  "NO_INTERNE" VARCHAR(15),
  "NO_EXTERNE" VARCHAR(15),
  "GENE" VARCHAR(15),
  "AUXI" VARCHAR(15),
  "LIBELLE" VARCHAR(80),
  "LETTRAGE" VARCHAR(4),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "VERROU" SMALLINT,
  "MONTANT" DECIMAL(12, 2)
)
AS
DECLARE VARIABLE SENS CHAR(1);
DECLARE VARIABLE LONGAUXI VARCHAR(80);
BEGIN
FOR SELECT L.MONTANT,L.SENS,L.LETTRAGE,L.LIBELLE,L.LIGNE,
M.DATE_MVT,M.NO_INTERNE,M.NO_EXTERNE,M.VERROU,
C.LIB_COURT,C1.LIB_COURT
from MOUVEMENTS M
    JOIN LIGNES L ON L.INDX_MVT=M.INDX
    JOIN COMPTES C ON C.INDX=L.INDX_RGP
    LEFT JOIN COMPTES C1 ON C1.INDX=L.INDX_CPT AND L.INDX_CPT<>L.INDX_RGP
    where M.INDX=:INDX_MVT
    INTO :MONTANT,:SENS,:LETTRAGE,:LIBELLE,:NOLIGNE,
         :DATE_MVT,:NO_INTERNE,:NO_EXTERNE,:VERROU,
         :GENE,:AUXI DO
  BEGIN
  IF (SENS='C') THEN
    BEGIN
    CREDIT = MONTANT;
    DEBIT = NULL;
    END
  ELSE
    BEGIN
    CREDIT=NULL;
    DEBIT = MONTANT;
    END
  SUSPEND;
  END
END
 ;


ALTER PROCEDURE "IS_EFF_MOUVEMENT_OK"
(
  "INDEX_MVT" SMALLINT
)
RETURNS
(
  "EFF_OK" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT VERROU FROM MOUVEMENTS WHERE INDX=:INDEX_MVT INTO :N;
IF (N<>0) THEN EFF_OK=0; ELSE EFF_OK=1;
SUSPEND;
END
 ;


ALTER PROCEDURE "LISTE_COMPTES"
(
  "AVEC_TIERS" SMALLINT
)
RETURNS
(
  "INDX" INTEGER,
  "INDX_GENE" INTEGER,
  "INDX_AUXI" INTEGER,
  "COMPTE" VARCHAR(15),
  "TIERS" VARCHAR(15),
  "DESCRIPTION" VARCHAR(80)
)
AS
begin
FOR SELECT LIB_COURT,LIB_LONG,INDX FROM COMPTES
WHERE REGROUPEMENT IS NULL
ORDER BY LIB_COURT
INTO :COMPTE,:DESCRIPTION,:INDX_GENE DO
  BEGIN
  INDX = INDX_GENE;
  INDX_AUXI = NULL;
  TIERS = NULL;
  SUSPEND;
  if (AVEC_TIERS=1) then
    begin
    FOR SELECT LIB_COURT,LIB_LONG,INDX FROM COMPTES
    WHERE REGROUPEMENT=:INDX_GENE
    ORDER BY LIB_COURT
    INTO :TIERS,:DESCRIPTION,:INDX_AUXI DO
      begin
      indx = indx_auxi;
      SUSPEND;
      end
    END
  END
end
 ;


ALTER PROCEDURE "LISTE_JOURNAUX_MENSUELS"
(
  "INDX_E" SMALLINT,
  "TYPE_LISTE" SMALLINT
)
RETURNS
(
  "INDX_JM" INTEGER,
  "MOIS" SMALLINT,
  "CODE_J" VARCHAR(4),
  "DESI" VARCHAR(80),
  "DATE_MODIF" DATE,
  "MONTANT" DECIMAL(12, 2),
  "DATE_PRN" DATE,
  "DATE_CLOTURE" DATE,
  "NBR" INTEGER,
  "NOMOIS" SMALLINT
)
AS
DECLARE VARIABLE ISOK SMALLINT;
DECLARE VARIABLE INDX_J INTEGER;
DECLARE VARIABLE INDX_JM1 INTEGER;
DECLARE VARIABLE N1 INTEGER;
begin
FOR SELECT JM.INDX,JM.MOIS,JM.NOMOIS,J.LONGDESI,J.SHORTDESI,J.INDX,
           JM.DATE_PRN,JM.DATE_CLOTURE,JM.NOMOIS FROM JOURNAL_MOIS JM
    LEFT JOIN JOURNAL J ON J.INDX=JM.INDX_JOURNAL
    WHERE INDX_EXERCICE=:INDX_E
/*    ORDER BY JM.NOMOIS */
    INTO :INDX_JM,:MOIS,:NOMOIS,:DESI,:CODE_J,:INDX_J,:DATE_PRN,:DATE_CLOTURE,:NOMOIS DO
  BEGIN
  SELECT COUNT(*) FROM MOUVEMENTS WHERE INDX_JOURNAL_M=:INDX_JM INTO :NBR;
  if (NBR>0) then SELECT MONTANT FROM GET_SOLDE_JOURNAL(:INDX_JM) INTO :MONTANT;
  ELSE MONTANT=NULL;
  SELECT MAX(DATE_MODIF) FROM MOUVEMENTS WHERE INDX_JOURNAL_M=:INDX_JM INTO :DATE_MODIF;
  ISOK=0;
  if ((TYPE_LISTE=0) or (NBR>0)) then ISOK=1;
  else IF (TYPE_LISTE=2) then
    BEGIN
    if (NOMOIS=0) then ISOK=1;
    else
      begin
      /* VΘrifier si l'avant dernier journal est vide */
      SELECT INDX FROM JOURNAL_MOIS
        WHERE INDX_EXERCICE=:INDX_E AND INDX_JOURNAL=:INDX_J AND NOMOIS=:NOMOIS-1
        INTO :INDX_JM1;
      SELECT COUNT(*) FROM MOUVEMENTS WHERE INDX_JOURNAL_M=:INDX_JM1 INTO :N1;
      if (N1>0) then ISOK=1;
      END
    END
  if (ISOK=1)  then SUSPEND;
  END
end
 ;


ALTER PROCEDURE "SET_IMPAIRE"
(
  "NO_JOURNAL_MOIS" SMALLINT
)
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE C1 SMALLINT;
DECLARE VARIABLE C2 SMALLINT;
BEGIN
C2=0;
FOR SELECT INDX,IMPAIRE FROM MOUVEMENTS WHERE INDX_JOURNAL_M=:NO_JOURNAL_MOIS ORDER BY DATE_MVT,INDX INTO :I,:C1 DO
  BEGIN
  IF ((C1 IS NULL) OR (C1<>C2)) THEN UPDATE MOUVEMENTS SET IMPAIRE=:C2 WHERE INDX=:I;
  IF (C2=0) THEN C2=1; ELSE C2=0;
  END
END
 ;


ALTER PROCEDURE "SOLDE_JM_COMPTE"
(
  "INDEX_JM" SMALLINT
)
RETURNS
(
  "INDEX_COMPTE" INTEGER,
  "CREDIT" DECIMAL(12, 2),
  "DEBIT" DECIMAL(12, 2)
)
AS
DECLARE VARIABLE SOLDE_COMPTE1 DECIMAL(12,2);
DECLARE VARIABLE SOLDE_COMPTE DECIMAL(12,2);
DECLARE VARIABLE INDX_J INTEGER;
BEGIN
SELECT INDX_JOURNAL,SOLDE_COMPTE FROM JOURNAL_MOIS WHERE INDX=:INDEX_JM INTO :INDX_J,:SOLDE_COMPTE;
SELECT INDX_COMPTE FROM JOURNAL WHERE INDX=:INDX_J INTO :INDEX_COMPTE;

SELECT SUM(L.MONTANT) FROM MOUVEMENTS M
JOIN LIGNES L ON L.INDX_MVT=M.INDX AND L.SENS='C' AND INDX_RGP=:INDEX_COMPTE
WHERE M.INDX_JOURNAL_M=:INDEX_JM
INTO :CREDIT;

SELECT SUM(L.MONTANT) FROM MOUVEMENTS M
JOIN LIGNES L ON L.INDX_MVT=M.INDX AND L.SENS='D' AND INDX_RGP=:INDEX_COMPTE
WHERE M.INDX_JOURNAL_M=:INDEX_JM
INTO :DEBIT;

IF (:CREDIT IS NULL) THEN CREDIT=0;
IF (:DEBIT IS NULL) THEN DEBIT=0;
IF (SOLDE_COMPTE IS NULL) THEN SOLDE_COMPTE=0;
SOLDE_COMPTE1 = CREDIT - DEBIT;
IF (SOLDE_COMPTE<>SOLDE_COMPTE1) THEN UPDATE JOURNAL_MOIS SET SOLDE_COMPTE=:SOLDE_COMPTE1 WHERE INDX=:INDEX_JM;
SUSPEND;
END
 ;


ALTER PROCEDURE "UPDATE_DATE_CLOTURE_JM"
(
  "INDX_JM" SMALLINT
)
AS
DECLARE VARIABLE NBR INTEGER;
DECLARE VARIABLE DATEC TIMESTAMP;
BEGIN
SELECT COUNT(*) FROM MOUVEMENTS WHERE VERROU=0 AND INDX_JOURNAL_M=:INDX_JM INTO :NBR;
SELECT DATE_CLOTURE FROM JOURNAL_MOIS WHERE INDX=:INDX_JM INTO DATEC;
IF ((NBR=0) AND (DATEC IS NULL)) THEN
  BEGIN
  UPDATE JOURNAL_MOIS SET DATE_CLOTURE='NOW' WHERE INDX=:INDX_JM;
  POST_EVENT 'RELISTER_CLOTURE';
  END
ELSE IF ((NBR>0) AND (DATEC IS NOT NULL)) THEN
  BEGIN
  UPDATE JOURNAL_MOIS SET DATE_CLOTURE=NULL WHERE INDX=:INDX_JM;
  POST_EVENT 'RELISTER_CLOTURE';
  END
END
 ;


ALTER PROCEDURE "VERROUILLE_JM"
(
  "INDX_JM" INTEGER
)
AS
BEGIN
UPDATE MOUVEMENTS SET VERROU=1,DATE_CLOTURE='NOW' WHERE VERROU=0 AND INDX_JOURNAL_M=:INDX_JM;
UPDATE JOURNAL_MOIS SET DATE_CLOTURE='NOW' WHERE INDX=:INDX_JM;
EXIT;
END
 ;


ALTER PROCEDURE "VERROUILLE_MVT"
(
  "INDX_MVT" INTEGER
)
AS
DECLARE VARIABLE NBR INTEGER;
DECLARE VARIABLE INDX_JM INTEGER;
BEGIN
SELECT COUNT(*) FROM MOUVEMENTS WHERE VERROU=0 AND INDX=:INDX_MVT INTO :NBR;
IF (NBR>0) THEN /* 1 normalement */
  BEGIN
  /* Verrouiller */
  UPDATE MOUVEMENTS SET VERROU=1,DATE_CLOTURE='NOW' WHERE INDX=:INDX_MVT;
  /* indiquer si le journal est verrouillΘ */
  SELECT INDX_JOURNAL_M FROM MOUVEMENTS WHERE INDX=:INDX_MVT INTO :INDX_JM;
  EXECUTE PROCEDURE UPDATE_DATE_CLOTURE_JM(:INDX_JM);
  END
EXIT;
END
 ;

COMMIT WORK;
SET AUTODDL ON;

/* Triggers only will work for SQL triggers */

CREATE TRIGGER "SETPK_BLOB" FOR "BLOBS"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."INDX" IS NULL) THEN
NEW."INDX" = GEN_ID(GENPK_BLOBS,1);
END
 ;

CREATE TRIGGER "BLOB_CHANGE" FOR "BLOBS"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
IF (new.indx=1) THEN POST_EVENT 'MODIF_BLOB1';
END
 ;

CREATE TRIGGER "SETPK_COMPTES" FOR "COMPTES"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."INDX" IS NULL) THEN
NEW."INDX" = GEN_ID(GENPK_COMPTES,1);
END
 ;

CREATE TRIGGER "RELISTE_COMPTES2" FOR "COMPTES"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_COMPTES';
END
 ;

CREATE TRIGGER "RELISTE_COMPTES3" FOR "COMPTES"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_COMPTES';
END
 ;

CREATE TRIGGER "RELISTE_COMPTES1" FOR "COMPTES"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_COMPTES';
END
 ;

CREATE TRIGGER "SETPK_ETAT" FOR "ETATS"
ACTIVE BEFORE INSERT POSITION 0
as
    begin
    if (new."INDX" IS NULL) THEN
    new."INDX" = GEN_ID(GENPK_ETATS,1);
    end
 ;

CREATE TRIGGER "RELISTE_ETATS2" FOR "ETATS"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_FONCTIONS';
POST_EVENT 'RELISTER_ETATS';
END
 ;

CREATE TRIGGER "RELISTE_ETATS3" FOR "ETATS"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
if (OLD.NOFONCTION<>NEW.NOFONCTION) then POST_EVENT 'RELISTER_FONCTIONS';
POST_EVENT 'RELISTER_ETATS';
END
 ;

CREATE TRIGGER "RELISTE_ETATS1" FOR "ETATS"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_FONCTIONS';
POST_EVENT 'RELISTER_ETATS';
END
 ;

CREATE TRIGGER "RELISTE_EXERCICE2" FOR "EXERCICE"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_EXERCICE';
END
 ;

CREATE TRIGGER "RELISTE_EXERCICE3" FOR "EXERCICE"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_EXERCICE';
END
 ;

CREATE TRIGGER "EFFACE_EXERCICE" FOR "EXERCICE"
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
DELETE FROM MOUVEMENTS WHERE INDX_EXERCICE=OLD.INDX;
DELETE FROM JOURNAL_MOIS WHERE INDX_EXERCICE=OLD.INDX;
END
 ;

CREATE TRIGGER "RELISTE_EXERCICE1" FOR "EXERCICE"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_EXERCICE';
END
 ;

CREATE TRIGGER "SETPK_JOURNAL" FOR "JOURNAL"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."INDX" IS NULL) THEN
NEW."INDX" = GEN_ID(GENPK_JOURNAL,1);
END
 ;

CREATE TRIGGER "RELISTE_JX2" FOR "JOURNAL"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_JOURNAUX';
END
 ;

CREATE TRIGGER "RELISTE_JX3" FOR "JOURNAL"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_JOURNAUX';
END
 ;

CREATE TRIGGER "EFFACE_JOURNAL" FOR "JOURNAL"
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
DELETE FROM JOURNAL_MOIS WHERE INDX_JOURNAL=OLD.INDX;
END
 ;

CREATE TRIGGER "RELISTE_JX1" FOR "JOURNAL"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_JOURNAUX';
END
 ;

CREATE TRIGGER "RELISTER_JM2" FOR "JOURNAL_MOIS"
ACTIVE AFTER INSERT POSITION 0
AS
begin
POST_EVENT 'RELISTER_JOURNAUX_MENSUELS';
end
 ;

CREATE TRIGGER "RELISTER_JM1" FOR "JOURNAL_MOIS"
ACTIVE AFTER UPDATE POSITION 0
AS
begin
POST_EVENT 'RELISTER_JOURNAUX_MENSUELS';
end
 ;

CREATE TRIGGER "EFFACE_JOURNAL_MOIS" FOR "JOURNAL_MOIS"
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
DELETE FROM MOUVEMENTS WHERE INDX_JOURNAL_M=OLD.INDX;
END
 ;

CREATE TRIGGER "RELISTER_JM3" FOR "JOURNAL_MOIS"
ACTIVE AFTER DELETE POSITION 0
AS
begin
POST_EVENT 'RELISTER_JOURNAUX_MENSUELS';
end
 ;

CREATE TRIGGER "LIGNES_BI0" FOR "LIGNES"
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if ((NEW.INDX_CPT IS NULL) or (new.INDX_CPT=0)) then NEW.INDX_CPT=NEW.INDX_RGP;
end
 ;

CREATE TRIGGER "LIGNES_BU0" FOR "LIGNES"
ACTIVE BEFORE UPDATE POSITION 0
AS
declare variable v SMALLINT;
begin
SELECT VERROU FROM MOUVEMENTS WHERE INDX=OLD.INDX_MVT INTO :v;
if (v=1) then EXCEPTION MAJ_VERROU;
if ((NEW.INDX_CPT IS NULL) or (new.INDX_CPT=0)) then NEW.INDX_CPT=NEW.INDX_RGP;
end
 ;

CREATE TRIGGER "LIGNES_BD0" FOR "LIGNES"
ACTIVE BEFORE DELETE POSITION 0
AS
declare variable v SMALLINT;
begin
SELECT VERROU FROM MOUVEMENTS WHERE INDX=OLD.INDX_MVT INTO :v;
if (v=1) then EXCEPTION DEL_VERROU;
end
 ;

CREATE TRIGGER "RELISTE_MODELES2" FOR "MODELES"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_MODELES';
END
 ;

CREATE TRIGGER "RELISTE_MODELES3" FOR "MODELES"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_MODELES';
END
 ;

CREATE TRIGGER "RELISTE_MODELES1" FOR "MODELES"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_MODELES';
END
 ;

CREATE TRIGGER "SETPK_MOUVEMENTS" FOR "MOUVEMENTS"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."INDX" IS NULL) THEN
NEW."INDX" = GEN_ID(GENPK_MOUVEMENTS,1);
END
 ;

CREATE TRIGGER "APRES_INSERT_MOUVEMENT" FOR "MOUVEMENTS"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
EXECUTE PROCEDURE UPDATE_DATE_CLOTURE_JM(NEW."INDX_JOURNAL_M");
END
 ;

CREATE TRIGGER "MOUVEMENTS_BU0" FOR "MOUVEMENTS"
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (OLD.VERROU=1) then EXCEPTION MAJ_VERROU;
end
 ;

CREATE TRIGGER "EFFACE_MOUVEMENT" FOR "MOUVEMENTS"
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
if (OLD.VERROU=1) then EXCEPTION DEL_VERROU;
DELETE FROM LIGNES WHERE INDX_MVT=OLD.INDX;
END
 ;

CREATE TRIGGER "APRES_EFFACE_MOUVEMENT" FOR "MOUVEMENTS"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
EXECUTE PROCEDURE UPDATE_DATE_CLOTURE_JM(OLD.INDX_JOURNAL_M);
END
 ;

CREATE TRIGGER "SETPK_TIERS" FOR "TIERS"
ACTIVE BEFORE INSERT POSITION 0
as
    begin
    if (new."INDX" IS NULL) THEN
    new."INDX" = GEN_ID(GENPK_TIERS,1);
    end
 ;

CREATE TRIGGER "SET_DATEMODIF" FOR "TIERS"
ACTIVE BEFORE UPDATE POSITION 0
as
    begin
    new."DATEMODIF" = 'NOW';
    end
 ;

CREATE TRIGGER "RELISTE_TVA2" FOR "TVA"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_TVA';
END
 ;

CREATE TRIGGER "RELISTE_TVA3" FOR "TVA"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_TVA';
END
 ;

CREATE TRIGGER "RELISTE_TVA1" FOR "TVA"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
POST_EVENT 'RELISTER_TVA';
END
 ;

COMMIT WORK ;

/* Grant Roles for this database */


/* Grant permissions for this database */